package Model;

import View.Admin;
import View.Course;
import View.Email;
import View.Student;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.Statement;
import java.io.Externalizable;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import sun.net.www.protocol.mailto.MailToURLConnection;

public class DBServices {

    private Connection conn;
    private Statement statement;
    private String sqlStatement;
    private ResultSet resultSet;
    public static DBServices object;
    
    
    

    
    public  DBServices() {

        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/sw","root", "");

            statement = (Statement) conn.createStatement();

        } catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex.getLocalizedMessage());
        } catch (ClassNotFoundException ex) {
            JOptionPane.showMessageDialog(null, ex.getLocalizedMessage());
        }
    }

    public static DBServices getObject() {
        if (object == null) {
            object = new DBServices();
            return object;
        } else {
            return object;
        }
    }
    
    
    
    private void openconection() {
        try {
            Class.forName("com.mysql.jdbc.Driver");
            conn = (Connection) DriverManager.getConnection("jdbc:mysql://localhost:3306/sw","root", "");

            statement = (Statement) conn.createStatement();
        } catch (SQLException ex) {
            System.out.println(ex.getLocalizedMessage());
        } catch (ClassNotFoundException ex) {
        }
        
    }
    
    public  Email   getMail(int id , int password){
    
        
        Email e = null;
        
        sqlStatement = "SELECT * FROM students where id ='" + id + "' and password = '"+ password +"' ;";
        try {

            if (conn.isClosed()) {
                openconection();
            }
           
            e  = new Email();
            resultSet = statement.executeQuery(sqlStatement);

           while (resultSet.next()) {
                
           int studentID = resultSet.getInt("id");
           String pass = resultSet.getString("password");
        //  int DBid = resultSet.getInt(2);
          e.setId(id);
          e.setPassword(pass);
          
          String fullName = resultSet.getString("full_name");
          
          e.setFullName(fullName);
        //  e.setId(DBid);
          
            }
            
           
            } catch (SQLException ex) {
            System.out.println("Error sql ");
            
        }
        
         return e;
    }
    
    public Admin getadmin() {
    
        Admin admin = null;
        
        
        sqlStatement = "select * from admin";
        try {
            if (conn.isClosed()) {
                openconection();
            }
             admin = new Admin();
            resultSet = statement.executeQuery(sqlStatement);
            while(resultSet.next()){
            
               String userName = resultSet.getString("user_name");
               String password = resultSet.getString("password");
               admin.setPassword(password);
               admin.setUserName(userName);
            }
            
            
        } catch (SQLException ex) {
            System.out.println("errorrrrrrrrr");
        }
      
        return admin;
        
    }
    
    public ArrayList<Course> getCourse(){
    
         ArrayList<Course> course = new ArrayList<Course>();
         
         
        sqlStatement = "SELECT * FROM courses  ";
         
        try {
            

            if (conn.isClosed()) {
                openconection();
            }
          
           
            Course coursename ;
            resultSet = statement.executeQuery(sqlStatement);

           while (resultSet.next()) {
         
               coursename = new Course();
               String  courseName = resultSet.getString("course_name");
               String courseCode = resultSet.getString("course_id");
               coursename.setId(courseCode);
               coursename.setName(courseName);
               course.add(coursename);
         
          

            }
           
            } catch (SQLException ex) {
            System.out.println("Error sql ");
            
        }
        
        return  course;
   
    
    }
    
    
    public Student getProfile (int id ){
    
        
         
        Student student = null;
        
        sqlStatement = "SELECT * FROM students where id = '"+ id +"' ;";
        try {

            if (conn.isClosed()) {
                openconection();
            }
           
            student = new Student();
            resultSet = statement.executeQuery(sqlStatement);

           while (resultSet.next()) {
                
          String mail = resultSet.getString("email");
          int DBid = resultSet.getInt(2);
          String fullName = resultSet.getString("full_name");
          String gender = resultSet.getString("gender");
          int level = resultSet.getInt("level");
          int phone = resultSet.getInt("phone");
          String address = resultSet.getString("address");
          
          student.setAddress(address);
          student.setEmail(mail);
          student.setFullName(fullName);
          student.setGender(gender);
          student.setId(DBid);
          student.setLevel(level);
          student.setPhone(phone);
          
          
        
          
            }
            
           
            } catch (SQLException ex) {
            System.out.println("Error sql ");
            
        }
        
        
        return student;
    
    
    }
    
    public boolean  regist(int id , String courseCode ){
    
        try {
        sqlStatement ="INSERT INTO student_courses  VALUES ( '"+id+ "','"+courseCode+"');";
        
        
        if (conn.isClosed()) {
                openconection();
            }
            if (statement.executeUpdate(sqlStatement) == 1) {

                statement.close();
                conn.close();
                return  true ;
            }

            return false ;
        } 
          catch (SQLException ex) {
            JOptionPane.showMessageDialog(null, ex.getLocalizedMessage());
              return  false ;
            
        }
      
    }
    
    public String courseCode(String courseName){
    
        String coursecode = null;
        
        
        sqlStatement = "SELECT * FROM courses where course_name ='" + courseName + "' ;";
        try {

            if (conn.isClosed()) {
                openconection();
            }
           
          
            resultSet = statement.executeQuery(sqlStatement);

           while (resultSet.next()) {
                
          coursecode = resultSet.getString("course_id");
        
          
            }
            
           
            } catch (SQLException ex) {
            System.out.println("Error sql 1111 ");
            
        }
        
         return coursecode;
        
    }
    
    
    public ArrayList<String> check (int id) {
    
       // ArrayList<String> allcourse = new ArrayList<String>();
        ArrayList<String> registedcourse = new ArrayList<String>();
        //ArrayList<String> allowedcourse = new ArrayList<String>();
        
                try {
            
            if (conn.isClosed()) {
                openconection();
            }
          
           
           sqlStatement = "select * from student_courses where stud_id = '"+id+"';";
           
           resultSet = statement.executeQuery(sqlStatement);
           
           while (resultSet.next()){
           
               String reg_courses = resultSet.getString("course_code");
               
              
               registedcourse.add(reg_courses);
               
           }
           
           
          
            } catch (SQLException ex) {
            System.out.println("Error sql ");
            
        }
                
                return registedcourse;
    
    }
    
    public String get(String coursecode){
    
        
        String coursename = null;
        
         sqlStatement = "SELECT course_name FROM courses where course_id ='" +coursecode+ "' ;";
        try {

            if (conn.isClosed()) {
                openconection();
            }
           
            
            resultSet = statement.executeQuery(sqlStatement);

           while (resultSet.next()) {
                
          coursename = resultSet.getString("course_name");
        
          
            }
            
           
            } catch (SQLException ex) {
            System.out.println("Error sql ");
            
        }
        
        
        return  coursename;
        
    }
    
    
    public int getcountcourses( int id ){
    
        int count = 0;
        
        
         sqlStatement = "SELECT count(course_code) as count FROM student_courses where stud_id ='" +id+ "' ;";
        try {

            if (conn.isClosed()) {
                openconection();
            }
           
            
            resultSet = statement.executeQuery(sqlStatement);

           
                while(resultSet.next()){
            count = resultSet.getInt("count");
            
                    }
            
            } catch (SQLException ex) {
            System.out.println("Error sql!!!!!!!!!!!!!!!!!!!!! ");
            
        }
    
        
        return count;
    }
    
    
     public void deleteCourses(ArrayList<String> list)
    {
        
        String courseID = null;
        try {
            for(int i =0 ; i < list.size()  ; i++ )
            {
                String name = list.get(i).toString();
              String query = "select course_code from student_courses join courses on student_courses.course_code=courses.course_id  where course_name= '" +name + "'";
               if (conn.isClosed()) {
                openconection();
              }
                 resultSet = statement.executeQuery(query);
             while (resultSet.next()) {
              courseID = resultSet.getString("course_code");
               }
            }
             String query = "delete from student_courses where course_code='"+ courseID+"'";
              if(statement.executeUpdate(query)== 0)
              System.out.println("error1111");
            
        } catch (SQLException ex) {
              System.out.println("11111111111111111111");
            }
    }
    
//     public Course[] getRegisteredCourse(int id) {
//        Course[] course = null;
//        sqlStatement = "select course_code,course_name from student_courses  join courses on courses.course_id=student_courses.course_code WHERE stud_id ='"+id+"'";
//        //sqlStatement = "select *from courses";
//        //WHERE stud_id ='"+student.getId()+"'
//        try {
//            if (conn.isClosed()) {
//                openconection();
//            }
//            ArrayList<Course> list = new ArrayList<Course>();
//            Course name;
//            resultSet = statement.executeQuery(sqlStatement);
//            while (resultSet.next()) {
//                name = new Course();
//                name.setId(resultSet.getString("id"));
//                name.setName(resultSet.getString("course_name"));
//                list.add(name);
//            }
//            course = new Course[list.size()];
//            list.toArray(course);
//        } catch (SQLException ex) {
//            System.out.println("Error sql ");
//        }
//        return course;
//    }
     
     
     public ArrayList<String> getregistedcourses(int id){
     
         ArrayList<String> list = new ArrayList<String>();
         sqlStatement ="select course_code , course_name from student_courses join courses on courses.course_id = student_courses.course_code where stud_id ='"+id+"' ";
         
          try {
            if (conn.isClosed()) {
                openconection();
            }
            resultSet = statement.executeQuery(sqlStatement);
            while(resultSet.next()){
            
               String courseName = resultSet.getString("course_name");
               list.add(courseName);
            }
            
            
          }
         catch (SQLException ex) {
            System.out.println("Error sql !!!!!!!!!!!!!!!!!!!");
        }
         
         return list;
     }
    
     
     public int update(Student item,String email,String oldPassword) throws SQLException {
        String updateQuery = "UPDATE students SET password = '" + item.getPassword() + "' WHERE email='"+email+"' and password = '" + oldPassword + "'";
        try {
            if (conn.isClosed()) {
                openconection();
            }
            updateQuery = updateQuery.replace("'null'", "null");
            int rowsAffected = statement.executeUpdate(updateQuery);
            statement.close();
            if (rowsAffected != 0) {
                return rowsAffected;
            } else {
                return -1;
            }
        } catch (SQLException e) {
            System.out.println("Error sql ");
        }
        return -1;
    }
   
}
